﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using FBWMS.Properties;
using System.Data.Linq;
using FBWMS.DataBase;
using System.Collections;

namespace FBWMS
{
    public class G_CommonClassDB
    {

        public G_CommonClassDB()
        {

        }
        ////////////////////////////////////////////////////////////////////////////
        // OutLine   : G_DB_SetSQLDBParameter
        // Parameter : ParameterName            :string                 :パラメータ名称
        //           : Direction                :ParameterDirection     :入出力の設定
        //           : DataType                 :SqlDbType              :データ型
        //           : DataSize                 :int                    :データサイズ
        //           : DataValue                :string                 :データの値
        // Return    : true Or false
        // Contents  : パラメータ設定関数
        // Makes     : 渡辺
        // History   : 2007/06/14       : 初版
        //           :
        ////////////////////////////////////////////////////////////////////////////
        public static SqlParameter G_DB_SetSQLDBParameter(string ParameterName, ParameterDirection Direction, SqlDbType DataType, int DataSize, string DataValue)
        {
            try
            {
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.ProcStart, Resources.ハラメータ設定開始, "G_DB_SetParameter");

                SqlParameter sqlPara = new SqlParameter();

                sqlPara.ParameterName = ParameterName;
                sqlPara.Direction = Direction;
                sqlPara.SqlDbType = DataType;
                sqlPara.Size = DataSize;
                sqlPara.Value = DataValue;


                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.ProcEnd, Resources.ハラメータ設定終了, "G_DB_SetParameter");
                return sqlPara;
            }
            catch (Exception err)
            {
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.Error, Resources.ハラメータ設定エラー + err.Message, "G_DB_SetParameter");
                return new SqlParameter();
            }
        }

        ////////////////////////////////////////////////////////////////////////////
        // OutLine   : G_DB_ExecStoredProc(オーバーライド1)
        // Parameter : connect            :SqlConnection        :接続情報
        //           : sqlCmd             :SqlCommand           :コマンド
        //           : str_StoredProcNM   :string               :実行ストアド名称
        //           : sqlPara            :SqlParameter[]       :設定するパラメータ(Input)
        //           : sqlOutPara         :out SqlParameter[]   :結果(Outputパラメータ)
        // Return    : G_CommonDefine.RetValueにセットする
        // Contents  : ストアド実行(パラメータ設定あり・OutPutパラメータ受け取り)
        // Makes     : 渡辺
        // History   : 2007/06/14       : 初版
        //           :
        ////////////////////////////////////////////////////////////////////////////
        public static G_CommonDefine.RetValue G_DB_ExecStoredProc(SqlConnection connect, SqlCommand sqlCmd, string str_StoredProcNM, SqlParameter[] sqlPara, out SqlParameter[] sqlOutPara)
        {
            try
            {
                //SqlParameterを作成し、配列で渡す
                //渡したParameterのうち、Direction が InputOutput・Output・ReturnValueのものは SqlParameter配列として返される


                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcStart, Resources.ストアト実行開始, "G_DB_ExecStoredProc : " + str_StoredProcNM);

                sqlCmd.CommandText = str_StoredProcNM;
                sqlCmd.CommandType = CommandType.StoredProcedure;


                for (int i = 0; i < sqlPara.Length; i++)
                {
                    sqlCmd.Parameters.Add(sqlPara[i]);
                }                

                sqlCmd.ExecuteReader();               

                List<SqlParameter> list = new List<SqlParameter>();
                for (int i = 0; i < sqlPara.Length; i++)
                {
                    if (sqlPara[i].Direction == ParameterDirection.Output | sqlPara[i].Direction == ParameterDirection.InputOutput | sqlPara[i].Direction == ParameterDirection.ReturnValue)
                    {
                        list.Add(sqlPara[i]);
                    }
                }
                sqlOutPara = list.ToArray();

                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcEnd, Resources.ストアト実行終了, "G_DB_ExecStoredProc : " + str_StoredProcNM);

                return G_CommonDefine.RetValue.GetData;
            }
            catch (Exception err)
            {
                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.Error, Resources.ストアト実行エラー + err.Message, "G_DB_ExecStoredProc : " + str_StoredProcNM);
                connect = null;
                sqlOutPara = new SqlParameter[0];
                return G_CommonDefine.RetValue.UndefinedError;
            }
        }
        ////////////////////////////////////////////////////////////////////////////
        // OutLine   : G_DB_ExecStoredProc(オーバーライド2)
        // Parameter : connect            :SqlConnection        :接続情報
        //           : sqlCmd             :SqlCommand           :コマンド
        //           : str_StoredProcNM   :string               :実行ストアド名称
        //           : sqlPara            :SqlParameter[]       :設定するパラメータ(Input)
        //           : ds                 :out DataSet          :結果セット
        // Return    : G_CommonDefine.RetValueにセットする
        // Contents  : ストアド実行(Inputパラメータ設定あり・結果セットを返す・OutPutパラメータ受け取りなし)
        // Makes     : 渡辺
        // History   : 2007/06/14       : 初版
        //           :
        ////////////////////////////////////////////////////////////////////////////
        public static G_CommonDefine.RetValue G_DB_ExecStoredProc(SqlConnection connect, SqlCommand sqlCmd, string str_StoredProcNM, SqlParameter[] sqlPara, out DataSet ds)
        {
            try
            {
                //SqlParameterを作成し、配列で渡す
                //渡したParameterのうち、Direction が InputOutput・Output・ReturnValueのものは SqlParameter配列として返される


                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcStart, Resources.ストアト実行開始, "G_DB_ExecStoredProc : " + str_StoredProcNM);

                sqlCmd.CommandText = str_StoredProcNM;
                sqlCmd.CommandType = CommandType.StoredProcedure;

                //SqlParameter[] clonedParameters = new SqlParameter[sqlPara.Length];

                //for (int i = 0, j = sqlPara.Length; i < j; i++)
                //{
                //    clonedParameters[i] = (SqlParameter)((ICloneable)sqlPara[i]).Clone();
                //}
                for (int i = 0; i < sqlPara.Length; i++)
                {
                    sqlCmd.Parameters.Add(sqlPara[i]);
                }

                DataSet data = new DataSet();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCmd;
                sqlDataAdapter.Fill(data);

                
                if (data.Tables[0].Rows.Count == 0)
                {
                    ds = null;
                    return G_CommonDefine.RetValue.NoData;
                }



                ds = data;


                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcEnd, Resources.ストアト実行終了, "G_DB_ExecStoredProc : " + str_StoredProcNM);

                return G_CommonDefine.RetValue.GetData;
            }
            catch (Exception err)
            {
                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.Error, Resources.ストアト実行エラー + err.Message, "G_DB_ExecStoredProc : " + str_StoredProcNM);
                connect = null;
                ds = null;
                return G_CommonDefine.RetValue.UndefinedError;
            }
        }
        ////////////////////////////////////////////////////////////////////////////
        // OutLine   : G_DB_ExecStoredProc(オーバーライド3)
        // Parameter : connect            :SqlConnection        :接続情報
        //           : ds                 :out DataSet          :結果セット
        //           : str_StoredProcNM   :string               :実行ストアド名称
        // Return    : G_CommonDefine.RetValueにセットする
        // Contents  : ストアド実行(パラメータなし)
        // Makes     : 渡辺
        // History   : 2007/06/14       : 初版
        //           :
        ////////////////////////////////////////////////////////////////////////////
        public static G_CommonDefine.RetValue G_DB_ExecStoredProc(SqlConnection connect, out DataSet ds, string str_StoredProcNM)
        {
            try
            {

                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcStart, Resources.ストアト実行開始, "G_DB_ExecStoredProc : " + str_StoredProcNM);

                SqlCommand sqlCmd = connect.CreateCommand();
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = str_StoredProcNM;

                DataSet data = new DataSet();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = sqlCmd;
                sqlDataAdapter.Fill(data);

                if (data.Tables[0].Rows.Count == 0)
                {
                    ds = null;
                    return G_CommonDefine.RetValue.NoData;
                }


                ds = data;

                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcEnd, Resources.ストアト実行終了, "G_DB_ExecStoredProc : " + str_StoredProcNM);

                return G_CommonDefine.RetValue.GetData;
            }
            catch (Exception err)
            {
                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.Error, Resources.ストアト実行エラー + err.Message, "G_DB_ExecStoredProc : " + str_StoredProcNM);
                connect = null;
                ds = null;

                return G_CommonDefine.RetValue.UndefinedError;
            }
        }
        ////////////////////////////////////////////////////////////////////////////
        // OutLine   : G_DB_Connect
        // Parameter : connect            :out SqlConnection        :生成したインスタンス
        // Return    : true Or false
        // Contents  : DB接続用インスタンスを生成する(同期型)
        // Makes     : 渡辺
        // History   : 2007/06/14       : 初版
        //           :
        ////////////////////////////////////////////////////////////////////////////
        public static bool G_DB_Connect(out SqlConnection connect)
        {
            try
            {
                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcStart, Resources.DB接続開始, "G_DB_Connect");
                
                string stConnectionString = string.Empty;
                stConnectionString += "Data Source = " + G_CommonDefine.G_DBServer + ";";
                stConnectionString += "Initial Catalog = " + G_CommonDefine.G_DBCatalog + ";";
                stConnectionString += "Integrated Security = " + G_CommonDefine.G_DBSecurity + ";";
                stConnectionString += "User ID = " + G_CommonDefine.G_DBUser + ";";
                stConnectionString += "Password = " + G_CommonDefine.G_DBPass + ";";

                stConnectionString += "Timeout = 300;";

                SqlConnection hConnection = (
                    new SqlConnection(stConnectionString)
                );
                hConnection.Open();

                connect = hConnection;


                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcEnd, Resources.DB接続終了, "G_DB_Connect");

                return true;
            }
            catch (Exception err)
            {
                //				MessageBox.Show(err.Message);
                connect = null;

                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.Error, Resources.DB接続エラー + err.Message, "G_DB_Connect");

                return false;
            }
        }
        ////////////////////////////////////////////////////////////////////////////
        // OutLine   : G_DB_Connect_NoSame
        // Parameter : connect            :out SqlConnection        :生成したインスタンス
        // Return    : true Or false
        // Contents  : DB接続用インスタンスを生成する(非同期型)
        // Makes     : 渡辺
        // History   : 2007/06/14       : 初版
        //           :
        ////////////////////////////////////////////////////////////////////////////
        public static bool G_DB_Connect_NoSame(out SqlConnection connect)
        {
            try
            {

                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcStart, Resources.DB接続開始, "G_DB_Connect");



                string stConnectionString = string.Empty;
                stConnectionString += "Data Source = " + G_CommonDefine.G_DBServer + ";";
                stConnectionString += "Initial Catalog = " + G_CommonDefine.G_DBCatalog + ";";
                stConnectionString += "Integrated Security = " + G_CommonDefine.G_DBSecurity + ";";
                stConnectionString += "User ID = " + G_CommonDefine.G_DBUser + ";";
                stConnectionString += "Password = " + G_CommonDefine.G_DBPass + ";";
                stConnectionString += "ASYNCHRONOUS PROCESSING=TRUE;";
                SqlConnection hConnection = (
                    new SqlConnection(stConnectionString)
                );
                hConnection.Open();

                connect = hConnection;


                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcEnd, Resources.DB接続終了, "G_DB_Connect");

                return true;
            }
            catch (Exception err)
            {
                //				MessageBox.Show(err.Message);
                connect = null;

                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.Error, Resources.DB接続エラー非同期 + err.Message, "G_DB_Connect");

                return false;
            }
        }

        ////////////////////////////////////////////////////////////////////////////
        // OutLine   : G_DB_Connect
        // Parameter : connect            :out SqlConnection        :生成したインスタンス
        // Return    : true Or false
        // Contents  : DB接続用インスタンスを生成する(同期型)
        // Makes     : 渡辺
        // History   : 2007/06/14       : 初版
        //           :
        ////////////////////////////////////////////////////////////////////////////
        public static bool G_DB_DataContext(out G_CommonDataBaseDataContext connect)
        {
            try
            {
                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcStart, Resources.DB接続開始, "G_DB_Connect");

                string stConnectionString = string.Empty;
                stConnectionString += "Data Source = " + G_CommonDefine.G_DBServer + ";";
                stConnectionString += "Initial Catalog = " + G_CommonDefine.G_DBCatalog + ";";
                stConnectionString += "Integrated Security = " + G_CommonDefine.G_DBSecurity + ";";
                stConnectionString += "User ID = " + G_CommonDefine.G_DBUser + ";";
                stConnectionString += "Password = " + G_CommonDefine.G_DBPass + ";";

                G_CommonDataBaseDataContext hConnection =
                    new G_CommonDataBaseDataContext(stConnectionString);
                hConnection.Connection.Open();

                connect = hConnection;


                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcEnd, Resources.DB接続終了, "G_DB_Connect");

                return true;
            }
            catch (Exception err)
            {
                //				MessageBox.Show(err.Message);
                connect = null;

                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.Error, Resources.DB接続エラー + err.Message, "G_DB_Connect");

                return false;
            }
        }
        ////////////////////////////////////////////////////////////////////////////
        // OutLine   : G_DB_Connect_NoSame
        // Parameter : connect            :SqlConnection        :破棄するsqlcinnection
        // Return    : true Or false
        // Contents  : DB接続用インスタンスを破棄する
        // Makes     : 渡辺
        // History   : 2007/06/14       : 初版
        //           :
        ////////////////////////////////////////////////////////////////////////////
        public static bool G_DB_Dispose(SqlConnection connect)
        {
            try
            {
                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcStart, Resources.DB接続解除開始, "G_DB_Dispose");

                connect.Close();
                connect.Dispose();

                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcEnd, Resources.DB接続解除終了, "G_DB_Dispose");
                return true;
            }
            catch (Exception err)
            {
                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.Error, Resources.DB接続解除エラー + err.Message, "G_DB_Dispose");

                return false;
            }
        }

        ////////////////////////////////////////////////////////////////////////////
        // OutLine   : G_DB_Connect_NoSame
        // Parameter : connect            :SqlConnection        :破棄するsqlcinnection
        // Return    : true Or false
        // Contents  : DB接続用インスタンスを破棄する
        // Makes     : 渡辺
        // History   : 2007/06/14       : 初版
        //           :
        ////////////////////////////////////////////////////////////////////////////
        public static bool G_DB_Dispose(G_CommonDataBaseDataContext connect)
        {
            try
            {
                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcStart, Resources.DB接続解除開始, "G_DB_Dispose");

                connect.Connection.Close();
                connect.Connection.Dispose();
                connect.Dispose();

                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.NetProcEnd, Resources.DB接続解除終了, "G_DB_Dispose");
                return true;
            }
            catch (Exception err)
            {
                //ログ書き込み
                G_CommonClassSystem.G_System_SetLog(G_CommonDefine.LogMode.Error, Resources.DB接続解除エラー + err.Message, "G_DB_Dispose");

                return false;
            }
        }

    }

        /**//**//**//// <summary> 
    /// DataSet助手 
    /// </summary> 
    public class DataSetHelper 
    { 
        private class FieldInfo 
        { 
            public string RelationName; 
            public string FieldName; 
            public string FieldAlias; 
            public string Aggregate; 
        } 
 
        private DataSet ds; 
        private ArrayList m_FieldInfo; 
        private string m_FieldList; 
        private ArrayList GroupByFieldInfo; 
        private string GroupByFieldList; 
 
        public DataSet DataSet 
        { 
            get { return ds; } 
        } 
 
        #region Construction 
 
        public DataSetHelper() 
        { 
            ds = null; 
        } 
 
        public DataSetHelper(ref DataSet dataSet) 
        { 
            ds = dataSet; 
        } 
 
        #endregion 
 
        #region Private Methods 
 
        private bool ColumnEqual(object objectA, object objectB) 
        { 
            if ( objectA == DBNull.Value && objectB == DBNull.Value ) 
            { 
                return true; 
            } 
            if ( objectA == DBNull.Value || objectB == DBNull.Value ) 
            { 
                return false; 
            } 
            return ( objectA.Equals( objectB ) ); 
        } 
 
        private bool RowEqual(DataRow rowA, DataRow rowB, DataColumnCollection columns) 
        { 
            bool result = true; 
            for ( int i = 0; i < columns.Count; i++ ) 
            { 
                result &= ColumnEqual( rowA[ columns[ i ].ColumnName ], rowB[ columns[ i ].ColumnName ] ); 
            } 
            return result; 
        } 
 
        private void ParseFieldList(string fieldList, bool allowRelation) 
        { 
            if ( m_FieldList == fieldList ) 
            { 
                return; 
            } 
            m_FieldInfo = new ArrayList(); 
            m_FieldList = fieldList; 
            FieldInfo Field; 
            string[] FieldParts; 
            string[] Fields = fieldList.Split( ',' ); 
            for ( int i = 0; i <= Fields.Length - 1; i++ ) 
            { 
                Field = new FieldInfo(); 
                FieldParts = Fields[ i ].Trim().Split( ' ' ); 
                switch ( FieldParts.Length ) 
                { 
                    case 1: 
                        //to be set at the end of the loop 
                        break; 
                    case 2: 
                        Field.FieldAlias = FieldParts[ 1 ]; 
                        break; 
                    default: 
                        return; 
                } 
                FieldParts = FieldParts[ 0 ].Split( '.' ); 
                switch ( FieldParts.Length ) 
                { 
                    case 1: 
                        Field.FieldName = FieldParts[ 0 ]; 
                        break; 
                    case 2: 
                        if ( allowRelation == false ) 
                        { 
                            return; 
                        } 
                        Field.RelationName = FieldParts[ 0 ].Trim(); 
                        Field.FieldName = FieldParts[ 1 ].Trim(); 
                        break; 
                    default: 
                        return; 
                } 
                if ( Field.FieldAlias == null ) 
                { 
                    Field.FieldAlias = Field.FieldName; 
                } 
                m_FieldInfo.Add( Field ); 
            } 
        } 
 
        private DataTable CreateTable(string tableName, DataTable sourceTable, string fieldList) 
        { 
            DataTable dt; 
            if ( fieldList.Trim() == "" ) 
            { 
                dt = sourceTable.Clone(); 
                dt.TableName = tableName; 
            } 
            else 
            { 
                dt = new DataTable( tableName ); 
                ParseFieldList( fieldList, false ); 
                DataColumn dc; 
                foreach ( FieldInfo Field in m_FieldInfo ) 
                { 
                    dc = sourceTable.Columns[ Field.FieldName ]; 
                    DataColumn column = new DataColumn(); 
                    column.ColumnName = Field.FieldAlias; 
                    column.DataType = dc.DataType; 
                    column.MaxLength = dc.MaxLength; 
                    column.Expression = dc.Expression; 
                    dt.Columns.Add( column ); 
                } 
            } 
            if ( ds != null ) 
            { 
                ds.Tables.Add( dt ); 
            } 
            return dt; 
        } 
 
        private void InsertInto(DataTable destTable, DataTable sourceTable, 
                                string fieldList, string rowFilter, string sort) 
        { 
            ParseFieldList( fieldList, false ); 
            DataRow[] rows = sourceTable.Select( rowFilter, sort ); 
            DataRow destRow; 
            foreach ( DataRow sourceRow in rows ) 
            { 
                destRow = destTable.NewRow(); 
                if ( fieldList == "" ) 
                { 
                    foreach ( DataColumn dc in destRow.Table.Columns ) 
                    { 
                        if ( dc.Expression == "" ) 
                        { 
                            destRow[ dc ] = sourceRow[ dc.ColumnName ]; 
                        } 
                    } 
                } 
                else 
                { 
                    foreach ( FieldInfo field in m_FieldInfo ) 
                    { 
                        destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ]; 
                    } 
                } 
                destTable.Rows.Add( destRow ); 
            } 
        } 
 
        private void ParseGroupByFieldList(string FieldList) 
        { 
            if ( GroupByFieldList == FieldList ) 
            { 
                return; 
            } 
            GroupByFieldInfo = new ArrayList(); 
            FieldInfo Field; 
            string[] FieldParts; 
            string[] Fields = FieldList.Split( ',' ); 
            for ( int i = 0; i <= Fields.Length - 1; i++ ) 
            { 
                Field = new FieldInfo(); 
                FieldParts = Fields[ i ].Trim().Split( ' ' ); 
                switch ( FieldParts.Length ) 
                { 
                    case 1: 
                        //to be set at the end of the loop 
                        break; 
                    case 2: 
                        Field.FieldAlias = FieldParts[ 1 ]; 
                        break; 
                    default: 
                        return; 
                } 
 
                FieldParts = FieldParts[ 0 ].Split( '(' ); 
                switch ( FieldParts.Length ) 
                { 
                    case 1: 
                        Field.FieldName = FieldParts[ 0 ]; 
                        break; 
                    case 2: 
                        Field.Aggregate = FieldParts[ 0 ].Trim().ToLower(); 
                        Field.FieldName = FieldParts[ 1 ].Trim( ' ', ')' ); 
                        break; 
                    default: 
                        return; 
                } 
                if ( Field.FieldAlias == null ) 
                { 
                    if ( Field.Aggregate == null ) 
                    { 
                        Field.FieldAlias = Field.FieldName; 
                    } 
                    else 
                    { 
                        Field.FieldAlias = Field.Aggregate + "of" + Field.FieldName; 
                    } 
                } 
                GroupByFieldInfo.Add( Field ); 
            } 
            GroupByFieldList = FieldList; 
        } 
 
        private DataTable CreateGroupByTable(string tableName, DataTable sourceTable, string fieldList) 
        { 
            if ( fieldList == null || fieldList.Length == 0 ) 
            { 
                return sourceTable.Clone(); 
            } 
            else 
            { 
                DataTable dt = new DataTable( tableName ); 
                ParseGroupByFieldList( fieldList ); 
                foreach ( FieldInfo Field in GroupByFieldInfo ) 
                { 
                    DataColumn dc = sourceTable.Columns[ Field.FieldName ]; 
                    if ( Field.Aggregate == null ) 
                    { 
                        dt.Columns.Add( Field.FieldAlias, dc.DataType, dc.Expression ); 
                    } 
                    else 
                    { 
                        dt.Columns.Add( Field.FieldAlias, dc.DataType ); 
                    } 
                } 
                if ( ds != null ) 
                { 
                    ds.Tables.Add( dt ); 
                } 
                return dt; 
            } 
        } 
 
        private void InsertGroupByInto(DataTable destTable, DataTable sourceTable, string fieldList, 
                                       string rowFilter, string groupBy) 
        { 
            if ( fieldList == null || fieldList.Length == 0 ) 
            { 
                return; 
            } 
            ParseGroupByFieldList( fieldList );  
            ParseFieldList( groupBy, false );  
            DataRow[] rows = sourceTable.Select( rowFilter, groupBy ); 
            DataRow lastSourceRow = null, destRow = null; 
            bool sameRow; 
            int rowCount = 0; 
            foreach ( DataRow sourceRow in rows ) 
            { 
                sameRow = false; 
                if ( lastSourceRow != null ) 
                { 
                    sameRow = true; 
                    foreach ( FieldInfo Field in m_FieldInfo ) 
                    { 
                        if ( !ColumnEqual( lastSourceRow[ Field.FieldName ], sourceRow[ Field.FieldName ] ) ) 
                        { 
                            sameRow = false; 
                            break; 
                        } 
                    } 
                    if ( !sameRow ) 
                    { 
                        destTable.Rows.Add( destRow ); 
                    } 
                } 
                if ( !sameRow ) 
                { 
                    destRow = destTable.NewRow(); 
                    rowCount = 0; 
                } 
                rowCount += 1; 
                foreach ( FieldInfo field in GroupByFieldInfo ) 
                { 
                    switch ( field.Aggregate.ToLower() ) 
                    { 
                        case null:  
                        case "": 
                        case "last": 
                            destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ]; 
                            break; 
                        case "first": 
                            if ( rowCount == 1 ) 
                            { 
                                destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ]; 
                            } 
                            break; 
                        case "count": 
                            destRow[ field.FieldAlias ] = rowCount; 
                            break; 
                        case "sum": 
                            destRow[ field.FieldAlias ] = Add( destRow[ field.FieldAlias ], sourceRow[ field.FieldName ] ); 
                            break; 
                        case "max": 
                            destRow[ field.FieldAlias ] = Max( destRow[ field.FieldAlias ], sourceRow[ field.FieldName ] ); 
                            break; 
                        case "min": 
                            if ( rowCount == 1 ) 
                            { 
                                destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ]; 
                            } 
                            else 
                            { 
                                destRow[ field.FieldAlias ] = Min( destRow[ field.FieldAlias ], sourceRow[ field.FieldName ] ); 
                            } 
                            break; 
                    } 
                } 
                lastSourceRow = sourceRow; 
            } 
            if ( destRow != null ) 
            { 
                destTable.Rows.Add( destRow ); 
            } 
        } 
 
        private object Min(object a, object b) 
        { 
            if ( ( a is DBNull ) || ( b is DBNull ) ) 
            { 
                return DBNull.Value; 
            } 
            if ( ( (IComparable) a ).CompareTo( b ) == -1 ) 
            { 
                return a; 
            } 
            else 
            { 
                return b; 
            } 
        } 
 
        private object Max(object a, object b) 
        { 
            if ( a is DBNull ) 
            { 
                return b; 
            } 
            if ( b is DBNull ) 
            { 
                return a; 
            } 
            if ( ( (IComparable) a ).CompareTo( b ) == 1 ) 
            { 
                return a; 
            } 
            else 
            { 
                return b; 
            } 
        } 
 
        private object Add(object a, object b) 
        { 
            if ( a is DBNull ) 
            { 
                return b; 
            } 
            if ( b is DBNull ) 
            { 
                return a; 
            } 
            return ( (decimal) a + (decimal) b ); 
        } 
 
        private DataTable CreateJoinTable(string tableName, DataTable sourceTable, string fieldList) 
        { 
            if ( fieldList == null ) 
            { 
                return sourceTable.Clone(); 
            } 
            else 
            { 
                DataTable dt = new DataTable( tableName ); 
                ParseFieldList( fieldList, true ); 
                foreach ( FieldInfo field in m_FieldInfo ) 
                { 
                    if ( field.RelationName == null ) 
                    { 
                        DataColumn dc = sourceTable.Columns[ field.FieldName ]; 
                        dt.Columns.Add( dc.ColumnName, dc.DataType, dc.Expression ); 
                    } 
                    else 
                    { 
                        DataColumn dc = sourceTable.ParentRelations[ field.RelationName ].ParentTable.Columns[ field.FieldName ]; 
                        dt.Columns.Add( dc.ColumnName, dc.DataType, dc.Expression ); 
                    } 
                } 
                if ( ds != null ) 
                { 
                    ds.Tables.Add( dt ); 
                } 
                return dt; 
            } 
        } 
 
        private void InsertJoinInto(DataTable destTable, DataTable sourceTable, 
                                    string fieldList, string rowFilter, string sort) 
        { 
            if ( fieldList == null ) 
            { 
                return; 
            } 
            else 
            { 
                ParseFieldList( fieldList, true ); 
                DataRow[] Rows = sourceTable.Select( rowFilter, sort ); 
                foreach ( DataRow SourceRow in Rows ) 
                { 
                    DataRow DestRow = destTable.NewRow(); 
                    foreach ( FieldInfo Field in m_FieldInfo ) 
                    { 
                        if ( Field.RelationName == null ) 
                        { 
                            DestRow[ Field.FieldName ] = SourceRow[ Field.FieldName ]; 
                        } 
                        else 
                        { 
                            DataRow ParentRow = SourceRow.GetParentRow( Field.RelationName ); 
                            DestRow[ Field.FieldName ] = ParentRow[ Field.FieldName ]; 
                        } 
                    } 
                    destTable.Rows.Add( DestRow ); 
                } 
            } 
        } 
 
        #endregion 
 
        #region SelectDistinct / Distinct 
 
        /**//**//**//// <summary> 
        /// 按照fieldName从sourceTable中选择出不重复的行， 
        /// 相当于select distinct fieldName from sourceTable 
        /// </summary> 
        /// <param name="tableName">表名</param> 
        /// <param name="sourceTable">源DataTable</param> 
        /// <param name="fieldName">列名</param> 
        /// <returns>一个新的不含重复行的DataTable，列只包括fieldName指明的列</returns> 
        public DataTable SelectDistinct(string tableName, DataTable sourceTable, string fieldName) 
        { 
            DataTable dt = new DataTable( tableName ); 
            dt.Columns.Add( fieldName, sourceTable.Columns[ fieldName ].DataType ); 
 
            object lastValue = null; 
            foreach ( DataRow dr in sourceTable.Select( "", fieldName ) ) 
            { 
                if ( lastValue == null || !( ColumnEqual( lastValue, dr[ fieldName ] ) ) ) 
                { 
                    lastValue = dr[ fieldName ]; 
                    dt.Rows.Add( new object[]{lastValue} ); 
                } 
            } 
            if ( ds != null && !ds.Tables.Contains( tableName ) ) 
            { 
                ds.Tables.Add( dt ); 
            } 
            return dt; 
        } 
 
        /**//**//**//// <summary> 
        /// 按照fieldName从sourceTable中选择出不重复的行， 
        /// 相当于select distinct fieldName1,fieldName2,,fieldNamen from sourceTable 
        /// </summary> 
        /// <param name="tableName">表名</param> 
        /// <param name="sourceTable">源DataTable</param> 
        /// <param name="fieldNames">列名数组</param> 
        /// <returns>一个新的不含重复行的DataTable，列只包括fieldNames中指明的列</returns> 
        public DataTable SelectDistinct(string tableName, DataTable sourceTable, string[] fieldNames) 
        { 
            DataTable dt = new DataTable( tableName ); 
            object[] values = new object[fieldNames.Length]; 
            string fields = ""; 
            for ( int i = 0; i < fieldNames.Length; i++ ) 
            { 
                dt.Columns.Add( fieldNames[ i ], sourceTable.Columns[ fieldNames[ i ] ].DataType ); 
                fields += fieldNames[ i ] + ","; 
            } 
            fields = fields.Remove( fields.Length - 1, 1 ); 
            DataRow lastRow = null; 
            foreach ( DataRow dr in sourceTable.Select( "", fields ) ) 
            { 
                if ( lastRow == null || !( RowEqual( lastRow, dr, dt.Columns ) ) ) 
                { 
                    lastRow = dr; 
                    for ( int i = 0; i < fieldNames.Length; i++ ) 
                    { 
                        values[ i ] = dr[ fieldNames[ i ] ]; 
                    } 
                    dt.Rows.Add( values ); 
                } 
            } 
            if ( ds != null && !ds.Tables.Contains( tableName ) ) 
            { 
                ds.Tables.Add( dt ); 
            } 
            return dt; 
        } 
 
        /**//**//**//// <summary> 
        /// 按照fieldName从sourceTable中选择出不重复的行， 
        /// 并且包含sourceTable中所有的列。 
        /// </summary> 
        /// <param name="tableName">表名</param> 
        /// <param name="sourceTable">源表</param> 
        /// <param name="fieldName">字段</param> 
        /// <returns>一个新的不含重复行的DataTable</returns> 
        public DataTable Distinct(string tableName, DataTable sourceTable, string fieldName) 
        { 
            DataTable dt = sourceTable.Clone(); 
            dt.TableName = tableName; 
 
            object lastValue = null; 
            foreach ( DataRow dr in sourceTable.Select( "", fieldName ) ) 
            { 
                if ( lastValue == null || !( ColumnEqual( lastValue, dr[ fieldName ] ) ) ) 
                { 
                    lastValue = dr[ fieldName ]; 
                    dt.Rows.Add( dr.ItemArray ); 
                } 
            } 
            if ( ds != null && !ds.Tables.Contains( tableName ) ) 
            { 
                ds.Tables.Add( dt ); 
            } 
            return dt; 
        } 
 
        /**//**//**//// <summary> 
        /// 按照fieldNames从sourceTable中选择出不重复的行， 
        /// 并且包含sourceTable中所有的列。 
        /// </summary> 
        /// <param name="tableName">表名</param> 
        /// <param name="sourceTable">源表</param> 
        /// <param name="fieldNames">字段</param> 
        /// <returns>一个新的不含重复行的DataTable</returns> 
        public DataTable Distinct(string tableName, DataTable sourceTable, string[] fieldNames) 
        { 
            DataTable dt = sourceTable.Clone(); 
            dt.TableName = tableName; 
            string fields = ""; 
            for ( int i = 0; i < fieldNames.Length; i++ ) 
            { 
                fields += fieldNames[ i ] + ","; 
            } 
            fields = fields.Remove( fields.Length - 1, 1 ); 
            DataRow lastRow = null; 
            foreach ( DataRow dr in sourceTable.Select( "", fields ) ) 
            { 
                if ( lastRow == null || !( RowEqual( lastRow, dr, dt.Columns ) ) ) 
                { 
                    lastRow = dr; 
                    dt.Rows.Add( dr.ItemArray ); 
                } 
            } 
            if ( ds != null && !ds.Tables.Contains( tableName ) ) 
            { 
                ds.Tables.Add( dt ); 
            } 
            return dt; 
        } 
 
        #endregion 
 
        #region Select Table Into 
 
        /**//**//**//// <summary> 
        /// 按sort排序，按rowFilter过滤sourceTable， 
        /// 复制fieldList中指明的字段的数据到新DataTable，并返回之 
        /// </summary> 
        /// <param name="tableName">表名</param> 
        /// <param name="sourceTable">源表</param> 
        /// <param name="fieldList">字段列表</param> 
        /// <param name="rowFilter">过滤条件</param> 
        /// <param name="sort">排序</param> 
        /// <returns>新DataTable</returns> 
        public DataTable SelectInto(string tableName, DataTable sourceTable, 
                                    string fieldList, string rowFilter, string sort) 
        { 
            DataTable dt = CreateTable( tableName, sourceTable, fieldList ); 
            InsertInto( dt, sourceTable, fieldList, rowFilter, sort ); 
            return dt; 
        } 
 
        #endregion 
 
        #region Group By Table 
 
        public DataTable SelectGroupByInto(string tableName, DataTable sourceTable, string fieldList, 
                                           string rowFilter, string groupBy) 
        { 
            DataTable dt = CreateGroupByTable( tableName, sourceTable, fieldList ); 
            InsertGroupByInto( dt, sourceTable, fieldList, rowFilter, groupBy ); 
            return dt; 
        } 
 
        #endregion 
 
        #region Join Tables 
 
        public DataTable SelectJoinInto(string tableName, DataTable sourceTable, string fieldList, string rowFilter, string sort) 
        { 
            DataTable dt = CreateJoinTable( tableName, sourceTable, fieldList ); 
            InsertJoinInto( dt, sourceTable, fieldList, rowFilter, sort ); 
            return dt; 
        } 
 
        #endregion 
 
        #region Create Table 
 
        public DataTable CreateTable(string tableName, string fieldList) 
        { 
            DataTable dt = new DataTable( tableName ); 
            DataColumn dc; 
            string[] Fields = fieldList.Split( ',' ); 
            string[] FieldsParts; 
            string Expression; 
            foreach ( string Field in Fields ) 
            { 
                FieldsParts = Field.Trim().Split( " ".ToCharArray(), 3 ); // allow for spaces in the expression 
                // add fieldname and datatype 
                if ( FieldsParts.Length == 2 ) 
                { 
                    dc = dt.Columns.Add( FieldsParts[ 0 ].Trim(), Type.GetType( "System." + FieldsParts[ 1 ].Trim(), true, true ) ); 
                    dc.AllowDBNull = true; 
                } 
                else if ( FieldsParts.Length == 3 ) // add fieldname, datatype, and expression 
                { 
                    Expression = FieldsParts[ 2 ].Trim(); 
                    if ( Expression.ToUpper() == "REQUIRED" ) 
                    { 
                        dc = dt.Columns.Add( FieldsParts[ 0 ].Trim(), Type.GetType( "System." + FieldsParts[ 1 ].Trim(), true, true ) ); 
                        dc.AllowDBNull = false; 
                    } 
                    else 
                    { 
                        dc = dt.Columns.Add( FieldsParts[ 0 ].Trim(), Type.GetType( "System." + FieldsParts[ 1 ].Trim(), true, true ), Expression ); 
                    } 
                } 
                else 
                { 
                    return null; 
                } 
            } 
            if ( ds != null ) 
            { 
                ds.Tables.Add( dt ); 
            } 
            return dt; 
        } 
 
        public DataTable CreateTable(string tableName, string fieldList, string keyFieldList) 
        { 
            DataTable dt = CreateTable( tableName, fieldList ); 
            string[] KeyFields = keyFieldList.Split( ',' ); 
            if ( KeyFields.Length > 0 ) 
            { 
                DataColumn[] KeyFieldColumns = new DataColumn[KeyFields.Length]; 
                int i; 
                for ( i = 1; i == KeyFields.Length - 1; ++i ) 
                { 
                    KeyFieldColumns[ i ] = dt.Columns[ KeyFields[ i ].Trim() ]; 
                } 
                dt.PrimaryKey = KeyFieldColumns; 
            } 
            return dt; 
        } 
 
        #endregion 
    }
}
